﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ConsoleApp1
{
    class Program
    {
        static void Main(string[] args)
        {
            //数据库连接
            var conString = "server=.;database=MySchool;uid=sa;pwd=123456";

            SqlConnection sqlConnection = new SqlConnection(conString);
            //启用数据库连接
            sqlConnection.Open();
            //数据库查询命令
            var cmdString = "select * from StuInfo";

            SqlCommand sqlCommand = new SqlCommand(cmdString,sqlConnection);

            var reader = sqlCommand.ExecuteReader();
            //打印数据库
            while (reader.Read())
            {
                Console.WriteLine("{0}  {1}  {2}",reader[0],reader[1],reader[2]);
            }
            //关闭数据库连接
            sqlConnection.Close();

            //建立表格
            DataTable table = new DataTable();
            //导入数据库数据到表格
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(cmdString,sqlConnection);

            sqlDataAdapter.Fill(table);
            //遍历打印
            foreach (DataRow row in table.Rows)
            {
                Console.WriteLine(row["StuId"]);
                Console.WriteLine(row["StuName"]);
                Console.WriteLine(row["StuSex"]);
            }

            //添加数据
            var cmdString2 = "insert StuInfo(StuId,StuName,StuSex)values(3,'小张','男')";
            sqlConnection.Open();
            SqlCommand sqlCommand1 = new SqlCommand(cmdString2, sqlConnection);
            var add = sqlCommand1.ExecuteNonQuery();
            sqlConnection.Close();

            //更新数据
            var cmdString3 = "update StuInfo set StuName='小林' where StuId =1;";
            sqlConnection.Open();
            SqlCommand sqlCommand2 = new SqlCommand(cmdString3, sqlConnection);
            var update = sqlCommand2.ExecuteNonQuery();
            sqlConnection.Close();

            //删除数据
            var cmdString4 = "delete StuInfo where StuId =1";
            sqlConnection.Open();
            SqlCommand sqlCommand3 = new SqlCommand(cmdString4, sqlConnection);
            var delete = sqlCommand3.ExecuteNonQuery();
            sqlConnection.Close();
        }
    }
}
